package com.ticketbooking.service.impl;

import com.ticketbooking.entity.ManagerEntity;
import com.ticketbooking.entity.ManagerInfEntity;
import com.ticketbooking.service.IManagerInfDao;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.sql.Types;
import java.util.List;
import java.util.Map;

@Service
@Transactional
public class ManagerInfDaoImpl implements IManagerInfDao {
    @Resource
    private JdbcTemplate jdbcTemplate;

    @Override
    public List getManagerInfByRolename(String rolename) {
        String sql = "select * from managerinf u where u.manrole = ?";
        return jdbcTemplate.queryForList(sql,new Object[]{rolename},new int[]{Types.VARCHAR});

    }

    @Override
    public ManagerInfEntity getManagerInfByManname(String manname) {
        String sql = "select * from managerinf u where u.manname = ?";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql,new Object[]{manname},new int[]{Types.VARCHAR});
        return getManInf(list);
    }
    @Override
    public List getManagerInf() {
        String sql = "select * from managerinf ";
        return jdbcTemplate.queryForList(sql);
    }

    @Override
    public List getRoleInf() {
        String sql = "SELECT k.rolecode rolecode,k.role role,count(m.manrole) num FROM (SELECT rolecode,role FROM roleinf) k LEFT JOIN (SELECT manrole FROM managerinf ) m ON k.role = m.manrole\n" +
                "GROUP BY k.rolecode ORDER BY num DESC;";
        return jdbcTemplate.queryForList(sql);
    }

    @Override
    public int delRoleInf(String rolecode) {
        String sql = "DELETE from roleinf where rolecode='"+rolecode+"'";
        String sql2 = "DELETE m,mi FROM manager m,managerinf mi WHERE mi.manname=m.manname and mi.manrole=(SELECT r.role from roleinf r WHERE r.rolecode='"+rolecode+"')";
        int result = jdbcTemplate.update(sql2);
        jdbcTemplate.update(sql);
        return result/2;
    }

    @Override
    public int addRoleInf(String rolecode, String rolename) {
        String sql = "INSERT roleinf(roleid,rolecode,role) VALUES(?,?,?)";
        Object args[] = {"0",rolecode,rolename};
        return jdbcTemplate.update(sql,args);
    }

    @Override
    public int delManger(String manname) {
        String sql = "DELETE mi,m FROM managerinf mi,manager m WHERE mi.manname=m.manname and m.manname='"+manname+"'";
        return jdbcTemplate.update(sql);
    }

    @Override
    public int updateManagerInf(ManagerInfEntity mi, ManagerEntity m) {
        String manname = mi.getManname();
        String sql = "UPDATE managerinf mi,manager m set mi.manname=?,mi.manrole=?,mi.realname=?,mi.manphone=?,m.manpermission=? where mi.manname='"+manname+"'";
        Object args[] = {mi.getManname(),mi.getManrole(),mi.getRealname(),mi.getManphone(),m.getManpermission()};
        int result = jdbcTemplate.update(sql,args);
        return result;
    }

    @Override
    public int addManager(ManagerInfEntity mi, ManagerEntity m) {
        String sql = "INSERT manager VALUE (?,?,?)";
        Object args[] = {mi.getManname(),m.getManpassword(),m.getManpermission()};
        int result = jdbcTemplate.update(sql,args);
        sql = "INSERT managerinf(manname,realname,manphone,manrole) VALUE (?,?,?,?)";
        Object args2[] = {mi.getManname(),mi.getRealname(),mi.getManphone(),mi.getManrole()};
        jdbcTemplate.update(sql,args2);
        return result;
    }

    private ManagerInfEntity getManInf( List<Map<String, Object>> manlist) {
        ManagerInfEntity maninf = new ManagerInfEntity();
        if (manlist.size() > 0) {
            Map<String, Object> map = manlist.get(0);
            maninf.setRealname((String) map.get("realname"));
            maninf.setManid((String) map.get("manid"));
            maninf.setManphone((String) map.get("manphone"));
            maninf.setManrole((String) map.get("manrole"));
            maninf.setManname((String) map.get("manname"));
        }
        return maninf;
    }
}
